package com.xmy.cultivate.mapper;

import com.xmy.cultivate.entity.CisPosition;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xmy.cultivate.entity.CisPositionDetail;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * 排班顺位表 Mapper 接口
 * </p>
 *
 * @author hpiggy
 * @since 2024-08-16
 */
public interface CisPositionMapper extends BaseMapper<CisPosition> {

    /**
     *
     * @param yearPart
     * @param quarterNum
     * @param schoolId
     * @param subjectsId
     * @param yearClassId
     * @param classTypeId
     * @param teacherId
     * @param isFull 1是满班人数，0是未满班人数，2是所有
     * @param limitNum
     * @return
     */
    @Select({"<script>",
            "SELECT",
                      "* ",
                    "FROM",
                      "(",
                      "SELECT",
                        "*,",
                        "( people_num / grade_count ) AS aver_count ",
                      "FROM",
                        "(",
                        "SELECT",
                          "t1.school_id,",
                          "t1.year_part,",
                          "t1.quarter_num,",
                          "t1.teacher_id,",
                          "t1.subjects_id,",
                          "t2.year_class_id,",
                          "t2.class_type_id,",
                          "t2.people_num,",
                          "t2.position_num,",
                          "t3.id AS grade_id,",
                          "t3.NAME AS grade_name,",
                          "t3.scheduling_type,",
                          "t3.week_dates,",
                          "t3.time_start,",
                          "t3.time_end,",
                          "t3.full_count AS grade_full_count,",
                          "(",
                          "SELECT",
                            "count(*) ",
                          "FROM",
                            "student_grade AS sg ",
                          "WHERE",
                            "sg.grade_id = t3.id ",
                            "AND sg.deleted = 0 ",
                            "AND (",
                              "sg.reading_status IN ( 1, 4, 5 ) ",
                            "OR ( sg.reading_status IN ( 2, 3 ) AND sg.use_course_count &gt; 0 ))) AS grade_people_count,",
                          "(",
                          "SELECT",
                            "count(*) ",
                          "FROM",
                            "grade AS gr ",
                          "WHERE",
                            "gr.year_part = t1.year_part ",
                            "AND gr.quarter_num = t1.quarter_num ",
                            "AND gr.subjects_id = t1.subjects_id ",
                            "AND gr.lesson_type = t1.lesson_type ",
                            "AND gr.teacher_id = t1.teacher_id ",
                            "AND gr.course_type_id = t2.class_type_id ",
                            "AND gr.year_class_id = t2.year_class_id ",
                            "AND gr.deleted = 0 ",
                          ") AS grade_count ",
                        "FROM",
                          "cis_position AS t1",
                          "INNER JOIN cis_position_detail AS t2 ON t1.id = t2.position_id ",
                          "AND t1.deleted = t2.deleted",
                          "INNER JOIN grade AS t3 ON t1.year_part = t3.year_part ",
                          "AND t1.quarter_num = t3.quarter_num ",
                          "AND t1.subjects_id = t3.subjects_id ",
                          "AND t2.year_class_id = t3.year_class_id ",
                          "AND t2.class_type_id = t3.course_type_id ",
                          "AND t1.teacher_id = t3.teacher_id ",
                          "AND t1.deleted = t3.deleted ",
                        "WHERE",
                          "t1.school_id = ${schoolId}",
                          "AND t1.year_part = ${yearPart} ",
                          "AND t1.quarter_num = ${quarterNum} ",
                            "AND t2.year_class_id = ${yearClassId} ",
                            "AND t1.subjects_id = ${subjectsId} ",
                          "AND t2.class_type_id = ${classTypeId} ",
                          "<when test='teacherId!=null and teacherId!=&apos;&apos;'>",
                            "AND t1.teacher_id = ${teacherId} ",
                          "</when>",
                          "AND t1.deleted = 0 ",
                        ") AS t01 ",
                      ") AS t ",
                    "WHERE",
                    "<when test='isFull == 1'>",
                        "grade_count &gt;= aver_count",
                    "</when>",
                    "<when test='isFull == 0'>",
                        "grade_people_count &lt; aver_count",
                    "</when>",
                    "ORDER BY",
                    "position_num ASC,",
                    "people_num DESC ",
                    "<when test='limitNum!=null and limitNum!=&apos;&apos;'>",
                    "limit ${limitNum}",
                    "</when>",
    "</script>"})
    public List<CisPosition> getPositionList(@Param("yearPart") Integer yearPart,
                                             @Param("quarterNum") Integer quarterNum,
                                             @Param("schoolId") Long schoolId,
                                             @Param("subjectsId") Long subjectsId,
                                             @Param("yearClassId") Integer yearClassId,
                                             @Param("classTypeId") Long classTypeId,
                                             @Param("teacherId") Long teacherId,
                                             @Param("isFull") Integer isFull,
                                             @Param("limitNum") Integer limitNum);

    @Select({"<script>",
            "SELECT",
            "grade_id",
            "FROM",
            "(",
            "SELECT",
            "*,",
            "( people_num / grade_count ) AS aver_count ",
            "FROM",
            "(",
            "SELECT",
            "t1.school_id,",
            "t1.year_part,",
            "t1.quarter_num,",
            "t1.teacher_id,",
            "t1.subjects_id,",
            "t2.year_class_id,",
            "t2.class_type_id,",
            "t2.people_num,",
            "t2.position_num,",
            "t3.id AS grade_id,",
            "t3.NAME AS grade_name,",
            "t3.scheduling_type,",
            "t3.week_dates,",
            "t3.time_start,",
            "t3.time_end,",
            "t3.full_count AS grade_full_count,",
            "(",
            "SELECT",
            "count(*) ",
            "FROM",
            "student_grade AS sg ",
            "WHERE",
            "sg.grade_id = t3.id ",
            "AND sg.deleted = 0 ",
            "AND (",
            "sg.reading_status IN ( 1, 4, 5 ) ",
            "OR ( sg.reading_status IN ( 2, 3 ) AND sg.use_course_count &gt; 0 ))) AS grade_people_count,",
            "(",
            "SELECT",
            "count(*) ",
            "FROM",
            "grade AS gr ",
            "WHERE",
            "gr.year_part = t1.year_part ",
            "AND gr.quarter_num = t1.quarter_num ",
            "AND gr.subjects_id = t1.subjects_id ",
            "AND gr.lesson_type = t1.lesson_type ",
            "AND gr.teacher_id = t1.teacher_id ",
            "AND gr.course_type_id = t2.class_type_id ",
            "AND gr.year_class_id = t2.year_class_id ",
            "AND gr.deleted = 0 ",
            ") AS grade_count ",
            "FROM",
            "cis_position AS t1",
            "INNER JOIN cis_position_detail AS t2 ON t1.id = t2.position_id ",
            "AND t1.deleted = t2.deleted",
            "INNER JOIN grade AS t3 ON t1.year_part = t3.year_part ",
            "AND t1.quarter_num = t3.quarter_num ",
            "AND t1.subjects_id = t3.subjects_id ",
            "AND t2.year_class_id = t3.year_class_id ",
            "AND t2.class_type_id = t3.course_type_id ",
            "AND t1.teacher_id = t3.teacher_id ",
            "AND t1.deleted = t3.deleted ",
            "WHERE",
            "t1.school_id = ${schoolId}",
            "AND t1.year_part = ${yearPart} ",
            "AND t1.quarter_num = ${quarterNum} ",
            "AND t1.subjects_id = ${subjectsId} ",
            "AND t2.year_class_id = ${yearClassId} ",
            "AND t2.class_type_id = ${classTypeId} ",
            "<when test='teacherId!=null and teacherId!=&apos;&apos;'>",
            "AND t1.teacher_id = ${teacherId} ",
            "</when>",
            "AND t1.deleted = 0 ",
            ") AS t01 ",
            ") AS t ",
            "WHERE",
            "grade_people_count &lt; aver_count",
            "ORDER BY",
            "position_num ASC,",
            "people_num DESC ",
            "<when test='limitNum!=null and limitNum!=&apos;&apos;'>",
            "limit ${limitNum}",
            "</when>",
            "</script>"})
    public List<Long> getPositionGradeIdList(@Param("yearPart") Integer yearPart,
                                             @Param("quarterNum") Integer quarterNum,
                                             @Param("schoolId") Long schoolId,
                                             @Param("subjectsId") Long subjectsId,
                                             @Param("yearClassId") Integer yearClassId,
                                             @Param("classTypeId") Long classTypeId,
                                             @Param("teacherId") Long teacherId,
                                             @Param("limitNum") Integer limitNum);

    @Select({"<script>",
            "SELECT",
                    "t1.*,",
                    "t2.class_type_id,",
                    "t2.people_num,",
                    "t2.position_num,",
                    "getSubjectsName(subjects_id) as subjects_name,",
                    "getCourseTypeName(class_type_id) as class_type_name",
                    "FROM",
                    "cis_position AS t1",
                    "INNER JOIN cis_position_detail AS t2 ON t1.id = t2.position_id",
                    "where 1=1",
                    "AND t1.school_id = ${schoolId}",
                    "AND t1.year_part = ${yearPart} ",
                    "AND t1.quarter_num = ${quarterNum} ",
                    "AND t1.subjects_id = ${subjectsId} ",
                    "AND t2.year_class_id = ${yearClassId} ",
                    "AND t2.class_type_id = ${classTypeId} ",
            "</script>"})
    public List<CisPosition> getPositionDataList(@Param("yearPart") Integer yearPart,
                                             @Param("quarterNum") Integer quarterNum,
                                             @Param("schoolId") Long schoolId,
                                             @Param("subjectsId") Long subjectsId,
                                             @Param("yearClassId") Integer yearClassId,
                                             @Param("classTypeId") Long classTypeId);



    //新增前 判断 position_num 是否重复
    @Select({"<script>",
            "SELECT ",
            "IFNULL(count(t1.id), 0)",
            "FROM",
            "cis_position AS t1",
            "INNER JOIN cis_position_detail AS t2 ON t2.position_id = t1.id and t2.deleted = 0",
            "where",
            "t1.deleted = 0",
            "and t1.year_part=${position.yearPart}",
            "and t1.quarter_num=${position.quarterNum}",
            "and t1.school_id=${position.schoolId}",
            "and t1.subjects_id=${position.subjectsId}",
            "and t2.year_class_id=${cisPositionDetail.yearClassId}",
            "and t2.class_type_id=${cisPositionDetail.classTypeId}",
            "and t2.position_num=${cisPositionDetail.positionNum}",
            "</script>"})
    public long getIsSaveYesOrNo(@Param("position") CisPosition position, @Param("cisPositionDetail") CisPositionDetail cisPositionDetail);

    //修改前 判断 position_num 是否重复
    @Select({"<script>",
            "SELECT ",
            "IFNULL(count(t1.id), 0)",
            "FROM",
            "cis_position AS t1",
            "INNER JOIN cis_position_detail AS t2 ON t2.position_id = t1.id and t2.deleted = 0",
            "where",
            "t1.deleted = 0",
            "and t2.id &lt;&gt; ${cisPositionDetail.id}",
            "and t1.year_part=${position.yearPart}",
            "and t1.quarter_num=${position.quarterNum}",
            "and t1.school_id=${position.schoolId}",
            "and t1.subjects_id=${position.subjectsId}",
            "and t2.year_class_id=${cisPositionDetail.yearClassId}",
            "and t2.class_type_id=${cisPositionDetail.classTypeId}",
            "and t2.position_num=${cisPositionDetail.positionNum}",
            "</script>"})
    public long getIsEditYesOrNo(@Param("position") CisPosition position, @Param("cisPositionDetail") CisPositionDetail cisPositionDetail);
}
